package com.huitongmaoyi.ai.util;

import java.sql.*;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

/**
 * 数据库元数据工具类：动态获取表结构、字段值分布等信息
 */
public class DBMetadataUtil {

    /**
     * 获取指定表的所有字段及类型
     */

    public static Map<String, String> getTableColumns(String tableName) {
        Map<String, String> columns = new HashMap<>();
        // 仅将 Connection 和 ResultSet 放在 try-with-resources 中（它们实现了 AutoCloseable）
        try (Connection conn = DBUtil.getConnection();
             ResultSet rs = conn.getMetaData().getColumns(null, null, tableName, null)) {

            // DatabaseMetaData 移出资源声明，直接在内部获取
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String typeName = rs.getString("TYPE_NAME");
                columns.put(columnName, typeName);
            }
        } catch (SQLException e) {
            System.err.println("获取表字段信息失败：" + e.getMessage());
        }
        return columns;
    }

    /**
     * 获取枚举类型字段的所有可能值（去重）
     */
    public static Map<String, Set<String>> getEnumFieldValues(String tableName, java.util.List<String> enumColumns) {
        Map<String, Set<String>> enumValues = new HashMap<>();
        if (enumColumns == null || enumColumns.isEmpty()) {
            return enumValues;
        }

        try (Connection conn = DBUtil.getConnection()) {
            for (String column : enumColumns) {
                String sql = "SELECT DISTINCT " + column + " FROM " + tableName + " WHERE " + column + " IS NOT NULL";
                try (PreparedStatement stmt = conn.prepareStatement(sql);
                     ResultSet rs = stmt.executeQuery()) {

                    Set<String> values = new HashSet<>();
                    while (rs.next()) {
                        values.add(rs.getString(column));
                    }
                    enumValues.put(column, values);
                } catch (SQLException e) {
                    System.err.println("获取字段[" + column + "]值失败：" + e.getMessage());
                }
            }
        } catch (SQLException e) {
            System.err.println("连接数据库失败：" + e.getMessage());
        }
        return enumValues;
    }

    /**
     * 获取数值型字段的最小/最大值范围
     */
    public static Map<String, double[]> getNumericFieldRanges(String tableName, java.util.List<String> numericColumns) {
        Map<String, double[]> ranges = new HashMap<>();
        if (numericColumns == null || numericColumns.isEmpty()) {
            return ranges;
        }

        try (Connection conn = DBUtil.getConnection()) {
            for (String column : numericColumns) {
                String sql = "SELECT MIN(" + column + "), MAX(" + column + ") FROM " + tableName + " WHERE " + column + " IS NOT NULL";
                try (PreparedStatement stmt = conn.prepareStatement(sql);
                     ResultSet rs = stmt.executeQuery()) {

                    if (rs.next()) {
                        double min = rs.getDouble(1);
                        double max = rs.getDouble(2);
                        // 处理NULL情况（如果表中无数据）
                        if (rs.wasNull()) {
                            ranges.put(column, new double[]{0, 0});
                        } else {
                            ranges.put(column, new double[]{min, max});
                        }
                    }
                } catch (SQLException e) {
                    System.err.println("获取字段[" + column + "]范围失败：" + e.getMessage());
                }
            }
        } catch (SQLException e) {
            System.err.println("连接数据库失败：" + e.getMessage());
        }
        return ranges;
    }
}